Ex009, Telephone directory (Using Query)
This is a second method for accessing database tables: using TQuery component instead
of TTable.
Query is like a Table, but it enables you to write SQL queries such as "Select *
from phone where name like '%John%';".
Exercise 009: Telephone directory (Using Query)
In this exercise you need to use last phone table you have designed in previous
exercise (007) if you do not; design a new one by following steps 1..7 of Ex007.
1. Drop a Query from Data Access page.
2. On Query's DataBase property select DBDEMOS.
3. On Query's SQL property write:
Select * from phone
4. Set Query's RequestLive property to True.
5. Drop DataSource from Data Access page.
6. Set DataSource's DataSet property to Query1.
7. Drop DBGrid from Data Controls page.
8. At DBGrid's DataSource property select DataSource1.
9. At Form1's OnCreate event write:
Query1.Open;
10. Drop a Button and name it 'btNew', Caption it '&New'.
11. At New button's OnClick event write:
Query1.Append;
DBGrid1.SetFocus;
12. Drop another Button and Name it 'btSave', Caption it '&Save'.
13. At Save button's OnClick event write:
Query1.FlushBuffers;
14. Run the program and enter new data.
To search on a table follow these steps:
15. Drop an Edit box from Standard page and name it 'edName', clear it's Text property.
16. At edName's OnKeyPress event write:
if Key = #13 then
begin
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('Select * from phone where Upper(Name) like "%'+
UpperCase(edName.Text)+'%"');
Query1.Open;
end; { if Key }
17. Drop another Button and name it 'edAll', Caption it 'Show &All'.
18. At edAll button's OnClick event write:
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('Select * from phone');
Query1.Open;
19. Drop a Label and name it 'laResult'.
20. At Query1's AfterOpen event write:
laResult.Caption:=
IntToStr(Query1.RecordCount);
21. Run the program and write any name or part of it at Edit Box then press enter.
Notes:
- SQL property in TQuery is a TStrings object so that it can be treated as any string
list object such as: Items object that exists in TListBox, TComboBox, and Lines object
that exists in TMemo, and TRichEdit. All these objects have Add, Delete, Clear, and
many other string list methods and properties.
- TDBGrid can used by TTable as the same way that used with TQuery.
- TDBEdit, TDBMemo, and other data controls can be used by Query object, but you
have to assign DataField property programatically (at run time) for TDBEdit, TDBMemo,
and other data controls such as:
Query1.Close;
Query1.SQL.Clear;
Query1.SQL.Add('Select * from phone');
DBEdit1.DataField:= 'Name';
DBEdit2.DataField:= 'Number';
DBEdit3.DataField:= 'Address';
DBMemo1.DataField:= 'Notes';
Query1.Open;
You can assign their DataSource property at design time.